﻿/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/****** Object:  Table [dbo].[Semnaturi]    Script Date: 03/26/2010 09:31:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
BEGIN TRANSACTION
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Semnaturi' AND COLUMN_NAME = 'Rand')
BEGIN
	DROP TABLE [Semnaturi]
END
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_admin_Audit_aspnet_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[admin_Audit]'))
begin
ALTER TABLE dbo.admin_Audit
	DROP CONSTRAINT FK_admin_Audit_aspnet_Users
end
GO

IF NOT EXISTS(select name from sysobjects where xtype='u' and name = 'Semnaturi')
BEGIN


CREATE TABLE [dbo].[Semnaturi](
	[IdSemnatura] [smallint] IDENTITY(1,1) NOT NULL,
	[Nume] [varchar](100) NOT NULL,
	[Functia] [varchar](50) NOT NULL,
	[Rand] [smallint] NOT NULL CONSTRAINT [DF_Semnaturi_Rand]  DEFAULT ((0)),
	[Coloana] [smallint] NOT NULL CONSTRAINT [DF_Semnaturi_Coloana]  DEFAULT ((0)),
 CONSTRAINT [PK_Semnaturi] PRIMARY KEY CLUSTERED 
(
	[IdSemnatura] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


END 
GO

/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Parteneri' AND COLUMN_NAME = 'Fax')
BEGIN
   ALTER TABLE dbo.Parteneri ADD
	Fax varchar(50) NULL
END
GO


/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Documente' AND COLUMN_NAME = 'NrContract')
BEGIN
   ALTER TABLE dbo.Documente ADD
	NrContract varchar(50) NULL,
	DataContract datetime NULL
END
GO

/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'AfiseazaProprietarDocument')
BEGIN
ALTER TABLE FaresSetup ADD
	AfiseazaProprietarDocument bit NOT NULL CONSTRAINT DF_FaresSetup_AfiseazaProprietarDocument DEFAULT 0
	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FaresSetup' AND COLUMN_NAME = 'FaraCodArticol')
BEGIN
ALTER TABLE dbo.FaresSetup ADD
	FaraCodArticol bit NOT NULL CONSTRAINT DF_FaresSetup_FaraCodArticol DEFAULT 0
	
END
GO



/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UMConversie' AND COLUMN_NAME = 'IDArticol')
BEGIN
ALTER TABLE dbo.UMConversie ADD
	IDArticol int NOT NULL CONSTRAINT DF_UMConversie_IDArticol DEFAULT 0
ALTER TABLE dbo.UMConversie
	DROP CONSTRAINT PK_UMConversie
ALTER TABLE dbo.UMConversie ADD CONSTRAINT
	PK_UMConversie PRIMARY KEY CLUSTERED 
	(
	IDUMSursa,
	IDUMDest,
	IDArticol
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
ALTER TABLE dbo.UMConversie ADD CONSTRAINT
	FK_UMConversie_Articole FOREIGN KEY
	(
	IDArticol
	) REFERENCES dbo.Articole
	(
	IDArticol
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO
/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'UnitatiMasura' AND COLUMN_NAME = 'Compusa')
BEGIN
ALTER TABLE UnitatiMasura ADD
	Compusa bit NOT NULL CONSTRAINT DF_UnitatiMasura_Compusa DEFAULT 0
END
GO

/*Verifica existenta coloanelor si le adauga*/
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Departamente' AND COLUMN_NAME = 'IDJurnal')
BEGIN
   ALTER TABLE dbo.Departamente ADD IDJurnal int NULL
   ALTER TABLE dbo.Departamente ADD CONSTRAINT
	FK_Departamente_Jurnale FOREIGN KEY
	(
	IDJurnal
	) REFERENCES dbo.Jurnale
	(
	IDJurnal
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
END
GO

/*--------------------------------------*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP VIEW [fares_FiltruDocumente]
GO
CREATE VIEW [fares_FiltruDocumente]
AS
SELECT     dbo.Documente.IDDocument, dbo.Documente.DataDoc, dbo.Documente.IDPartener, dbo.DocumenteDetaliu.IDArticol, dbo.Documente.CodUnitate
FROM         dbo.Documente INNER JOIN
                      dbo.DocumenteDetaliu ON dbo.Documente.IDDocument = dbo.DocumenteDetaliu.IDDocument

GO
/*------------------------------------------------*/
DROP PROCEDURE fares_FiltreazaDocumente 
GO
CREATE PROCEDURE fares_FiltreazaDocumente 
	-- Add the parameters for the stored procedure here
	@filtru varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @sel varchar(2000)
	SET @sel = 'SELECT * FROM [Documente] WHERE [IDDocument] IN (SELECT [IDDocument] FROM [fares_FiltruDocumente] WHERE ' +
	@filtru + ')'
	EXEC(@sel)
END
GO

/****** Object:  View [dbo].[fares_viewSumeCategorii]    Script Date: 03/25/2010 11:07:58 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[fares_viewSumeCategorii]'))
DROP VIEW [dbo].[fares_viewSumeCategorii]
GO

/****** Object:  View [dbo].[fares_viewSumeCategorii]    Script Date: 03/25/2010 11:07:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[fares_viewSumeCategorii]
AS
SELECT     dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, SUM(dbo.DocumenteDetaliu.ValoareTotala) AS ValoareTotala, dbo.DocumenteDetaliu.CodUnitate, 
                      dbo.Articole.Denumire AS DenumireArticol, dbo.Departamente.Nume AS DenDepartament, dbo.Jurnale.Denumire AS DenumireJurnal
FROM         dbo.Articole INNER JOIN
                      dbo.DocumenteDetaliu ON dbo.Articole.IDArticol = dbo.DocumenteDetaliu.IDArticol INNER JOIN
                      dbo.TipCategoriiArticole ON dbo.Articole.IDCategorie = dbo.TipCategoriiArticole.IDCategorie INNER JOIN
                      dbo.Documente ON dbo.DocumenteDetaliu.IDDocument = dbo.Documente.IDDocument LEFT OUTER JOIN
                      dbo.Departamente ON dbo.Documente.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.Jurnale ON dbo.Departamente.IDJurnal = dbo.Jurnale.IDJurnal
WHERE     (dbo.Documente.IDTipDocument = 9)
GROUP BY dbo.TipCategoriiArticole.Denumire, dbo.Documente.DataDoc, dbo.DocumenteDetaliu.CodUnitate, dbo.Articole.Denumire, dbo.Departamente.Nume, 
                      dbo.Jurnale.Denumire

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaCodArticol]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaCodArticol]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaCodArticol]
	-- Add the parameters for the stored procedure here
	@radacina varchar(10),
	@codNou varchar(20) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @CodReturnat varchar(20)
	DECLARE @CodReturnatNumar varchar(20)
	DECLARE @valNumar decimal(18,0)
    -- Insert statements for procedure here
	SELECT @CodReturnat=Max(codarticol) from articole where codarticol like @radacina + '%'
	if @CodReturnat is null
	begin 
		SET @CodReturnat = @radacina + '001'
	end
	else
	begin
		SET @CodReturnatNumar = REPLACE(@CodReturnat,@radacina,'')
		SET @valNumar = CAST(@CodReturnatNumar as decimal(18,0)) + 1
		SET @CodReturnat = CAST(@valNumar as varchar)
		SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
	end
	SET @codNou = @CodReturnat
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_ExistaLocalitate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_ExistaLocalitate]
GO

CREATE PROCEDURE [dbo].[fares_ExistaLocalitate]
	-- Add the parameters for the stored procedure here
	@nume varchar(50), 
	@idjudet int,
	@idloc int out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @idJ int;
	IF NOT EXISTS(SELECT * FROM [Judete] WHERE [IDJudet]=@idjudet)
		set @idloc=-1
    -- Insert statements for procedure here
	SELECT @idloc = [IDLocalitate] from [Localitati] WHERE UPPER(Nume) like UPPER(@nume) + '%' AND [IDJudet]=@idjudet
	IF @idloc is null
	begin
		INSERT INTO [Localitati]
           ([Nume],[CodPostal],[IDJudet],[PrefixTelefonic])
		VALUES
           (@nume,'00000',@idJudet,'00000')
		SELECT @idloc = @@IDENTITY
	end
END
GO


IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Mesaje') AND type in (N'U'))
BEGIN
	CREATE TABLE dbo.Mesaje
		(
		IdMesaj bigint NOT NULL IDENTITY (1, 1),
		UserDest uniqueidentifier NOT NULL,
		UserExp uniqueidentifier NOT NULL,
		Subiect varchar(250) NOT NULL,
		DataExp datetime NOT NULL,
		Corp text NOT NULL,
		DataCitirii datetime NULL
		)  ON [PRIMARY]
		 TEXTIMAGE_ON [PRIMARY]
	
	ALTER TABLE dbo.Mesaje ADD CONSTRAINT
		DF_Table_1_DataExp DEFAULT getdate() FOR DataExp
	
	ALTER TABLE dbo.Mesaje ADD CONSTRAINT
		PK_Table_1 PRIMARY KEY CLUSTERED 
		(
		IdMesaj
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


END
GO
COMMIT
